﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Data;

namespace DBETLTools
{
  public  class DemoMysql
    {
        private UInt64 ICopy = 1;
        MySqlConnection cns = new MySqlConnection();
        MySqlConnection cnt = new MySqlConnection();

        public DemoMysql(String s, string t)
        {
            cns.ConnectionString = s;
            cnt.ConnectionString = t;
        }

         

        public void Install(DBConfig dBConfig)
        {
            DBToSql dBToSql = new DBToSql();
            //创建触发存储表
            String cmd=  dBToSql.CREATET1();
            this.Execsql(cmd,cns);
           
            //创建 触发器
            foreach (DBTable item in dBConfig.DBTables)
            {
                foreach (DBTRIGGER titem in item.dBTRIGGERs)
                {
                    switch (titem)
                    {
                        case DBTRIGGER.INSERT:
                            cmd=  dBToSql.Mysqlinserttrigger(cns.Database+"."+item.SourceName, gettkey(cns.Database,item.SourceName));
                            break;
                        case DBTRIGGER.UPDATE:
                            cmd = dBToSql.MysqlUPDATEtrigger(cns.Database + "." + item.SourceName, gettkey(cns.Database,item.SourceName));
                            break;
                        case DBTRIGGER.DELETE:
                            cmd = dBToSql.MysqlDELETEtrigger(cns.Database + "." + item.SourceName, gettkey(cns.Database,item.SourceName));
                            break;
                        default:
                            cmd = "";
                            break;
                    }
                    this.Execsql(cmd,cns);
                    
                }
                
            }



        }
        public void RunCpoy(int m = 2000)
        {
            
            DBToSql dBToSql = new DBToSql();
            
            //查询 触发后记录
            using (MySqlDataAdapter dataAdapter = new MySqlDataAdapter("select  * from " + dBToSql.T1 + "  LIMIT 1", cns))
            {
                string Sqlcmd = "";
                using (DataSet dataSet = new DataSet())
                {

                    dataAdapter.Fill(dataSet);
                    foreach (DataRow item in dataSet.Tables[0].Rows)
                    {

                        switch (item.Field<string>("m"))
                        {
                            case "INSERT"://增加
                                Sqlcmd = dBToSql.Insert(getrow(item.Field<string>("t"), item.Field<string>("p"), item.Field<string>("v")));
                                break;
                            case "UPDATE"://更新
                                Sqlcmd = dBToSql.Update(getrow(item.Field<string>("t"), item.Field<string>("p"), item.Field<string>("v")), gettkey(cnt.Database, item.Field<string>("t")));
                                break;
                            case "DELETE"://删除
                                Sqlcmd = dBToSql.Delete(item.Field<string>("t"), item.Field<string>("p"), item.Field<string>("v"));
                                break;

                            default://错误的
                                break;
                        }
                        this.Execsql(Sqlcmd, cnt);
                        //删除以传递的数据
                        Sqlcmd = "delete from "+dBToSql.T1+" where   id= "+ item.Field<UInt64>("id").ToString();
                        this.Execsql(Sqlcmd, cns);
                        Console.WriteLine("本次以复制第 " + ICopy.ToString() + " 条数据");
                        ICopy++;

                    }
                }

            }
            System.Threading.Thread.Sleep(m);
            RunCpoy(m);
        }

        public string gettkey(string db, string t)
        {
            string t1 = "";
            if(t.Split('.').Length>1)
            {
                t1 = t.Split('.')[1];
            }
            else
            {
                t1 = t;
            }

            using (MySqlCommand da = new MySqlCommand("select  COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE where  TABLE_SCHEMA ='" + db + "' and TABLE_NAME='" + t1 + "' LIMIT  1", cns))
            {
                if(cns.State!=ConnectionState.Open)
                {
                    cns.Open();
                }
              return  da.ExecuteScalar().ToString();
            }
        }
        public DataRow getrow(string t, string key, string v)
        {
            using (MySqlDataAdapter da = new MySqlDataAdapter("select * from " + t + " where " + key + "='" + v + "'", cns))
            {
                using (DataSet ds = new DataSet())
                {
                    da.Fill(ds, t);
                    return ds.Tables[0].Rows[0];
                }
            }
        }
        public int Execsql(string Sql,MySqlConnection cn)
        {
            using (MySqlCommand cmd = new MySqlCommand(Sql, cn))
            {
                if (cn.State != ConnectionState.Open)
                {
                    cn.Open();
                }
                return cmd.ExecuteNonQuery();
            }
        }
        public List<string> GetTABLES(String db)
        {
            List<String> tables = new List<string>();
            String Sql = "SELECT * FROM  information_schema.TABLES Where  TABLE_SCHEMA = '" + db + "'";
            using (MySqlDataAdapter da = new MySqlDataAdapter(Sql, cns))
            {
                using (DataSet ds = new DataSet())
                {
                    da.Fill(ds);
                    foreach (DataRow item in ds.Tables[0].Rows)
                    {
                        tables.Add(item.Field<string>("TABLE_NAME"));
                    }

                }
                return tables;

            }



        }
        public Boolean Ist(String Table, DBTRIGGER mode)
        {

            List<String> tables = new List<string>();
            String Sql = "SELECT * FROM  information_schema.TRIGGERS Where  EVENT_OBJECT_TABLE = '" + Table + "' and EVENT_MANIPULATION='" + mode + "'";
            using (MySqlDataAdapter da = new MySqlDataAdapter(Sql, cns))
            {
                using (DataSet ds = new DataSet())
                {
                    da.Fill(ds);
                    return ds.Tables[0].Rows.Count == 1 ? true : false;

                }


            }

        }
    }
}
